﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using LinqToExcel;

namespace FlightService
{
    /// <summary>
    /// Algorithm that looks for any available flights in the specified order
    /// </summary>
    public class DefaultAlgorithm : IAlgorithm
    {
        private List<Flight> requestedFlights;
        public List<Flight> RequestedFlights
        {
            get
            {
                return requestedFlights;
            }
            private set { requestedFlights = value; }
        }

        private static string selectString 
        {
            get
            {
                return "SELECT DateFrom, DateTo, TimeFrom, TimeTo, " +
                       "CityFrom, CityTo, DisplayName as 'Airline', " +
                       "Price, PriceChildren, Adults, Children " +
                       "FROM [dbo].[Flights] F JOIN [dbo].[Airlines] A " +
                       "ON F.Airline_id = A.Id ";
            }
        }

        public string Execute(Request request, SqlConnection cnn)
        {
            requestedFlights = request.Flights;
            if (requestedFlights == null)
                return null;

            string response = string.Empty;
            var flights = new List<Flight>();

            StringBuilder sql; 

            for (int i = 0; i < requestedFlights.Count - 1; i++)
            {
                sql = new StringBuilder();
                sql.Append(selectString +
                           "WHERE CityFrom='" + 
                           requestedFlights[i].CityFrom + "' AND " +
                           "DateFrom='" + requestedFlights[i].DateFrom + "' AND " +
                           "Adults>=" + request.Adults + " AND " +
                           "Children>=" + request.Children + " AND " +
                           "CityTo='" + requestedFlights[i+1].CityFrom + "'");

                var cmd = new SqlCommand(sql.ToString(), cnn);
                var adp = new SqlDataAdapter(cmd);
                var ds = new DataSet();
                adp.Fill(ds);

                var flightsFromDb = Converter.ToFlights(ds.Tables[0]);
                if (flightsFromDb != null && flightsFromDb.Count > 0)
                    flights.Add(flightsFromDb[0]);
            }
            if (request.IsReturn)
            {
                sql = new StringBuilder();
                sql.Append(selectString +
                           "WHERE CityFrom='" +
                           requestedFlights.Last().CityFrom + "' AND " +
                           "DateFrom='" + requestedFlights.Last().DateFrom + "' AND " +
                           "Adults>=" + request.Adults + " AND " +
                           "Children>=" + request.Children + " AND " +
                           "CityTo='" + requestedFlights.First().CityFrom + "'");

                var cmd = new SqlCommand(sql.ToString(), cnn);
                var adp = new SqlDataAdapter(cmd);
                var ds = new DataSet();
                adp.Fill(ds);

                var flightsFromDb = Converter.ToFlights(ds.Tables[0]);
                if (flightsFromDb != null && flightsFromDb.Count > 0)
                    flights.Add(flightsFromDb[0]);
            }
            if ((!request.IsReturn && flights.Count != requestedFlights.Count - 1) || (request.IsReturn && flights.Count != requestedFlights.Count))
                return "No flights found";

            foreach (Flight f in flights)
            {
                f.DateFrom = new DateTime(f.DateFrom.Year, f.DateFrom.Month, f.DateFrom.Day,
                                          int.Parse(String.Concat(f.TimeFrom[0], f.TimeFrom[1])),
                                          int.Parse(String.Concat(f.TimeFrom[3], f.TimeFrom[4])),
                                          0);
                f.DateTo = new DateTime(f.DateTo.Year, f.DateTo.Month, f.DateTo.Day,
                                        int.Parse(String.Concat(f.TimeTo[0], f.TimeTo[1])),
                                        int.Parse(String.Concat(f.TimeTo[3], f.TimeTo[4])),
                                        0);
            }
            foreach (Flight f in flights)
            {
                f.Adults = request.Adults;
                f.Children = request.Children;
                f.TimeFrom = "";
                f.TimeTo = "";
            }
            response = XmlParser.FlightsToXml(flights);

            return response == string.Empty ? "No flights found" : response;
        }

        public string Execute(Request request, string filepath) 
        {
            requestedFlights = request.Flights;
            if (requestedFlights == null)
                return null;

            string response = string.Empty;
            List<Flight> flights = new List<Flight>();

            var excel = new ExcelQueryFactory(filepath);

            for (int i = 0; i < requestedFlights.Count - 1; i++)
            {
                var flightsSelectedFromFile = from c in excel.Worksheet<Flight>()
                                              where c.CityFrom == requestedFlights[i].CityFrom
                                              select c;

                var flightFiltered = flightsSelectedFromFile.FirstOrDefault(row => row.DateFrom == requestedFlights[i].DateFrom
                                                                                   && row.Adults >= request.Adults
                                                                                   && row.Children >= request.Children
                                                                                   && row.CityFrom == requestedFlights[i].CityFrom
                                                                                   && row.CityTo == requestedFlights[i + 1].CityFrom);
                if (flightFiltered != null)
                    flights.Add(flightFiltered);
            }
            if (request.IsReturn)
            {
                var flightsSelectedFromFile = from c in excel.Worksheet<Flight>()
                                              where c.CityFrom == requestedFlights.Last().CityFrom
                                              select c;

                var flightFiltered = flightsSelectedFromFile.FirstOrDefault(row => row.DateFrom == requestedFlights.Last().DateFrom
                                                                                   && row.Adults >= request.Adults
                                                                                   && row.Children >= request.Children
                                                                                   && row.CityFrom == requestedFlights.Last().CityFrom
                                                                                   && row.CityTo == requestedFlights.First().CityFrom);
                if (flightFiltered != null)
                    flights.Add(flightFiltered);
            }
            if ((!request.IsReturn && flights.Count != requestedFlights.Count - 1) || (request.IsReturn && flights.Count != requestedFlights.Count))
                return "No flights found";

            foreach (Flight f in flights)
            {
                f.DateFrom = new DateTime(f.DateFrom.Year, f.DateFrom.Month, f.DateFrom.Day,
                                          int.Parse(String.Concat(f.TimeFrom[11], f.TimeFrom[12])),
                                          int.Parse(String.Concat(f.TimeFrom[14], f.TimeFrom[15])),
                                          int.Parse(String.Concat(f.TimeFrom[17], f.TimeFrom[18])));
                f.DateTo = new DateTime(f.DateTo.Year, f.DateTo.Month, f.DateTo.Day,
                                        int.Parse(String.Concat(f.TimeTo[11], f.TimeTo[12])),
                                        int.Parse(String.Concat(f.TimeTo[14], f.TimeTo[15])),
                                        int.Parse(String.Concat(f.TimeTo[17], f.TimeTo[18])));
            }
            foreach (Flight f in flights)
            {
                f.Adults = request.Adults;
                f.Children = request.Children;
                f.TimeFrom = "";
                f.TimeTo = "";
            }
            response = XmlParser.FlightsToXml(flights);

            return response == string.Empty ? "No flights found" : response;
        }
    }
}